package chapterSystem.server;

import chapterSystem.client.entities.UnitInfo;
import chapterSystem.client.model.entitiesInt.*;
import chapterSystem.server.builder.EntityBuilder;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.security.NoSuchAlgorithmException;
import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * User: Hasp
 * Date: 28.02.12
 * Time: 12:10
 */
public class ModelConnector {
    private static ModelConnector instance;

    public static ModelConnector getInstance() {
        if (instance != null) {
            return instance;
        }

        try {
            instance = new ModelConnector();
            instance.createQueries();
        } catch (SQLException ignored) {
            ignored.printStackTrace();
        } catch (NamingException e) {
            e.fillInStackTrace();
        }

        return instance;
    }

    private Connection conn;
    private Map<String, PreparedStatement> queries;


    public ModelConnector() throws SQLException, NamingException {
        try {
            Class.forName("com.mysql.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        if (System.getProperty("dbdebug") != null) {
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/inf_sys", "root", "753357abcd");
        } else {
            Context ctx = new InitialContext();
            DataSource source = (DataSource) ctx.lookup("MySQLConn");
            conn = source.getConnection();
        }
        queries = new HashMap<String, PreparedStatement>();
    }

    public Brother login(String username, String enteredPass) throws SQLException {
        PreparedStatement ps = queries.get("login");
        ps.setString(1, username);

        ResultSet result = ps.executeQuery();
        if (!result.next()) {
            return null;
        }

        String password = result.getString("password");

        try {
            MessageDigest messageDigest = MessageDigest.getInstance("MD5");
            String enteredPassHash = new BigInteger(1, messageDigest.digest(enteredPass.getBytes())).toString(16);

            return password.equals(enteredPassHash) ?
                    EntityBuilder.getBrotherEntity(result)
                    :  null;
        } catch (NoSuchAlgorithmException ignored) {
            ignored.printStackTrace();
        }

        return null;
    }

    public Brother getById(Integer id) throws SQLException {
        PreparedStatement ps = queries.get("get_by_id");
        ps.setInt(1, id);

        ResultSet rs = ps.executeQuery();
        if (!rs.next()) {
            return null;
        }

        return EntityBuilder.getBrotherEntity(rs);
    }

    public Map<Integer, List<Brother>> getBrothersByUnitId() throws SQLException {
        PreparedStatement ps = queries.get("get_brothers_by_unit");
        ResultSet rs = ps.executeQuery();
        Map<Integer, List<Brother>> brothers = new HashMap<Integer, List<Brother>>();

        while (rs.next()) {
            final Brother brother = EntityBuilder.getBrotherEntity(rs);
            if (brothers.containsKey(brother.getUnitId())) {
                brothers.get(brother.getUnitId()).add(brother);
            } else {
                final List<Brother> b = new ArrayList<Brother>();
                b.add(brother);
                brothers.put(brother.getUnitId(), b);
            }
        }

        return brothers;
    }

    public Map<String, Unit> getAllUnits() throws SQLException {
        System.out.println("Getting all units...");
        final ResultSet rs = queries.get("units").executeQuery();
        final Map<String, Unit> units = new HashMap<String, Unit>();

        while (rs.next()) {
            final Unit newUnit = EntityBuilder.getUnitEntity(rs);
            units.put(newUnit.getName(), newUnit);
        }

        return units;
    }

    public UnitInfo getUnitInfo(int userId) throws SQLException {
        final Brother brother = getById(userId);
        System.out.println("Getting brother: " + brother.getName());

        final ResultSet rs = queries.get("units").executeQuery();
        final Map<Integer, Unit> units = new HashMap<Integer, Unit>();
        Unit root = null;
        Unit current = null;
        final Set<Unit> submission = new HashSet<Unit>();
        final Map<Integer, List<Brother>> brothers = getBrothersByUnitId();

        while (rs.next()) {
            final Unit newUnit = EntityBuilder.getUnitEntity(rs);
            if (brothers.containsKey(newUnit.getId())) {
                newUnit.addBrothers(brothers.get(newUnit.getId()));
            }
            if (newUnit.getParentId() == 0) {
                root = newUnit;
            }
            if (newUnit.getId() == brother.getUnitId()) {
                current = newUnit;
            }
            if (newUnit.getCommander().getId() == userId) {
                submission.add(newUnit);
            }
            units.put(newUnit.getId(), newUnit);

        }

        for (final Unit unit : units.values()) {
            final int parentId = unit.getParentId();
            if (units.containsKey(parentId)) {
                units.get(parentId).addSubunits(unit);
            }
        }

        addSubmissionUnits(userId, submission, root);

        return new UnitInfo(root, current, new ArrayList<Unit>(submission));
    }

    private void addSubmissionUnits(final int brother, final Set<Unit> submission, final Unit root) {
        if (root.getCommander().getId() == brother) {
            submission.add(root);
            addChildrenUnits(submission, root);
        }

        for (final Unit unit : root.getSubunits()) {
            if (unit.getCommander().getId() == brother) {
                submission.add(unit);
                addChildrenUnits(submission, unit);
            } else {
                addSubmissionUnits(brother, submission, unit);
            }
        }
    }

    private void addChildrenUnits(final Set<Unit> submission, final Unit root) {
        for (final Unit unit : root.getSubunits()) {
            submission.add(unit);
            addChildrenUnits(submission, unit);
        }
    }


    public List<Report> getReportsById(Integer id) throws SQLException {
        PreparedStatement ps = queries.get("get_report_by_author");
        ps.setInt(1, id);

        ResultSet rs = ps.executeQuery();
        ArrayList<Report> reports = new ArrayList<Report>();


        while (rs.next()) {
            reports.add(EntityBuilder.getReportEntity(rs));
        }


        return reports;
    }

    public List<Order> getOrdersByAuthor(Integer id) throws SQLException {
        PreparedStatement ps = queries.get("get_order_by_author");
        ps.setInt(1, id);

        ResultSet rs = ps.executeQuery();
        ArrayList<Order> orders = new ArrayList<Order>();

        while (rs.next()) {
            orders.add(EntityBuilder.getOrderEntity(rs));
        }

        return orders;
    }

    public List<Order> getOrdersByReceiver(Integer id) throws SQLException {
        PreparedStatement ps = queries.get("get_order_by_receiver");
        ps.setInt(1, id);

        ResultSet rs = ps.executeQuery();
        ArrayList<Order> orders = new ArrayList<Order>();

        while (rs.next()) {
            orders.add(EntityBuilder.getOrderEntity(rs));
        }

        return orders;
    }

    public List<ChapterEvent> getEvents(int eventsCount, java.sql.Date fromDate, java.sql.Date toDate) throws SQLException {
        PreparedStatement ps = queries.get("get_events");
        if (fromDate == null) {
            ps.setDate(1, java.sql.Date.valueOf("0001-01-01"));
        } else {
            ps.setDate(1, fromDate);
        }
        if (toDate == null) {
            ps.setDate(2, new java.sql.Date(System.currentTimeMillis()));
        } else {
            ps.setDate(2, toDate);
        }
        ps.setInt(3, eventsCount);

        ResultSet rs = ps.executeQuery();

        ArrayList<ChapterEvent> events = new ArrayList<ChapterEvent>();
        while (rs.next()) {
            events.add(EntityBuilder.getEventEntity(rs));
        }

        return events;
    }

    public String addNewBrother(Brother brother, String password) throws SQLException {
        PreparedStatement addBrother = queries.get("create_brother_entry");
        PreparedStatement getId = queries.get("select_max_brother_id");
        PreparedStatement addAccount = queries.get("create_account_entry");

        ResultSet rs = getId.executeQuery();
        if (!rs.next()) return null;

        final int id = rs.getInt(1) + 1;
        addBrother.setInt(1, id);
        addBrother.setString(2, brother.getName());
        addBrother.setInt(3, brother.getRankBattle());
        addBrother.setInt(4, brother.getRankWork());
        addBrother.setInt(5, brother.getUnitId()); //TODO
        addBrother.setInt(6, 0); //TODO
        addBrother.setInt(7, brother.getPoints());

        addBrother.execute();

        if (password != null) {
            addAccount.setInt(1, id);
            addAccount.setString(2, password);
            addAccount.execute();
        }


        return "Ok";
    }

    public String createBattleOrder(Order order) throws SQLException {
        final PreparedStatement ps = queries.get("get_max_orders_id");
        ResultSet rs = ps.executeQuery();
        int i = rs.next() ? rs.getInt(1) : -1;

        final PreparedStatement insertOrder = queries.get("insert_battle_order");
        insertOrder.setInt(1, i + 1);
        insertOrder.setInt(2, order.getAuthorId());
        insertOrder.setInt(3, order.getReceiverId());
        insertOrder.setString(4, order.getText());
        insertOrder.setDate(5, new Date(System.currentTimeMillis()));

        insertOrder.execute();

        return "Submitted";
    }

    public String editBattleOrderText(Order order) throws SQLException {
        final PreparedStatement ps = queries.get("edit_battle_order_text");
        ps.setString(1, order.getText());
        ps.setInt(2, order.getId());

        ps.execute();
        return "Ok";
    }

    public String createOrderExecutionReport(Order order, String briefReport) throws SQLException {
        final PreparedStatement ps = queries.get("get_max_execution_id");
        ResultSet rs = ps.executeQuery();
        int i = rs.next() ? rs.getInt(1) : -1;

        final PreparedStatement insertReportStatement = queries.get("insert_order_execution");
        insertReportStatement.setInt(1, i + 1);
        insertReportStatement.setInt(2, order.getId());
        insertReportStatement.setString(3, briefReport);
        insertReportStatement.setDate(4, new Date(System.currentTimeMillis()));

        insertReportStatement.execute();

        return "Submitted";
    }

    public String editExecutionReport(Order order) throws SQLException {
        final PreparedStatement ps = queries.get("edit_execution_report");
        ps.setString(1, order.getText());
        ps.setInt(2, order.getId());

        ps.execute();
        return "Ok";
    }

    public void addLog(final int brotherId, final int activityId, final String message) throws SQLException {
        final PreparedStatement st = queries.get("add_log");
        final PreparedStatement getId = queries.get("select_max_logg_id");

        final ResultSet rs = getId.executeQuery();
        final int entryId = !rs.next() ? 0 : rs.getInt(1) + 1;
        st.setInt(1, entryId);
        st.setInt(2, brotherId);
        st.setInt(3, activityId);
        st.setString(4, message);

        st.execute();
    }

    public Brother getBrother(final String name) throws SQLException {
        final PreparedStatement ps = queries.get("get_brother_by_login");
        ps.setString(1, name);
        final ResultSet rs = ps.executeQuery();
        if (!rs.next()) {
            return null;
        }

        return EntityBuilder.getBrotherEntity(rs);
    }

    public List<String> getBrotherLog(int brotherId) throws SQLException {
        final PreparedStatement ps = queries.get("see_brother_log");
        ps.setInt(1, brotherId);
        final ResultSet rs = ps.executeQuery();
        final ArrayList<String> result = new ArrayList<String>();
        int i = 1;

        while (rs.next()) {
            result.add(i++ + ".  " + rs.getString("activity.description") + "   " + rs.getDate("brother_activity.creation_date"));
        }

        return result;
    }

    public String submitReport(int brotherId, int eventId, String reportText) throws SQLException {
        final PreparedStatement idPs = queries.get("select_max_report_id");
        final ResultSet rs = idPs.executeQuery();
        final int entryId = !rs.next() ? 0 : rs.getInt(1) + 1;

        final PreparedStatement ps = queries.get("insert_report");
        ps.setInt(1, entryId);
        ps.setInt(2, brotherId);
        ps.setInt(3, eventId);
        ps.setString(4, reportText);

        ps.execute();
        return "Ok";
    }

    public List<Report> getReportsByEvent(int eventId, int brotherRank) throws SQLException {
        final PreparedStatement ps = queries.get("get_reports_by_event");
        ps.setInt(1, eventId);
        ps.setInt(2, brotherRank);

        ResultSet rs = ps.executeQuery();
        ArrayList<Report> reports = new ArrayList<Report>();

        while (rs.next()) {
            reports.add(EntityBuilder.getReportEntity(rs));
        }

        return reports;
    }

    public String editReport(Report report) throws SQLException {
        final PreparedStatement ps = queries.get("edit_report");
        ps.setString(1, report.getReport());
        ps.setInt(2, report.getId());

        ps.executeUpdate();
        return "Ok";
    }

    public String editBrother(Brother brother) throws SQLException {
        final PreparedStatement ps = queries.get("edit_brother");
        ps.setString(1, brother.getName());
        ps.setInt(2, brother.getRankBattle());
        ps.setInt(3, brother.getRankWork());
        ps.setInt(4, brother.getPoints());
        ps.setInt(5, brother.getUnitId());
        ps.setInt(6, brother.getId());

        ps.execute();
        return "Ok";
    }

    public String createEvent(ChapterEvent event) throws SQLException {
        final PreparedStatement idPs = queries.get("select_max_event_id");
        final ResultSet rs = idPs.executeQuery();
        final int entryId = !rs.next() ? 0 : rs.getInt(1) + 1;

        final PreparedStatement ps = queries.get("create_event");
        ps.setInt(1, entryId);
        ps.setString(2, event.getTitle());
        ps.setString(3, event.getDescription());
        ps.setDate(4, new Date(event.getCreationDate().getTime()));

        ps.execute();
        return "Ok";
    }


    private void createQueries() throws SQLException {
        queries.put("login", conn.prepareStatement("SELECT * FROM brother JOIN account ON " +
                "brother.brother_id = account.brother_id WHERE `name` = ?"));
        queries.put("get_by_id", conn.prepareStatement("SELECT * FROM brother WHERE brother_id = ?"));
        queries.put("get_order_by_author", conn.prepareStatement("SELECT * FROM battle_order LEFT JOIN order_execution ON " +
                "order_execution.order_id = battle_order.order_id JOIN brother ON author_id = brother.brother_id  " +
                "JOIN brother AS receiver ON receiver.brother_id = receiver_id" +
                " WHERE battle_order.author_id = ?"));
        queries.put("get_order_by_receiver", conn.prepareStatement("SELECT * FROM battle_order LEFT JOIN order_execution ON " +
                "order_execution.order_id = battle_order.order_id JOIN brother ON author_id = brother.brother_id  " +
                "JOIN brother AS receiver ON receiver.brother_id = receiver_id" +
                " WHERE battle_order.receiver_id = ?"));
        queries.put("get_report_by_author", conn.prepareStatement("SELECT * FROM battle_report WHERE " +
                "battle_report.author_id = ?"));
        queries.put("get_events", conn.prepareStatement("SELECT * FROM chapter_event WHERE (DATE(chapter_event.creation_date) BETWEEN ? AND ?) ORDER BY creation_date DESC LIMIT ?"));
        queries.put("create_brother_entry", conn.prepareStatement("INSERT INTO brother values (?, ?, ?, ?, ?, ?, ?)"));
        queries.put("create_account_entry", conn.prepareStatement("INSERT INTO account values (?, ?)"));
        queries.put("select_max_brother_id", conn.prepareStatement("SELECT MAX(brother_id) FROM brother"));
        queries.put("get_brothers_by_unit", conn.prepareStatement("select * from brother"));
        queries.put("get_brother_by_login", conn.prepareStatement("select * from brother where name=?"));
        queries.put("units", conn.prepareStatement("select * from unit join brother on (brother.brother_id=unit.commander_id)"));
        queries.put("get_units_by_id", conn.prepareStatement("select * from unit join brother on (brother.brother_id=unit.commander_id) where unit.unit_id=?"));
        queries.put("add_log", conn.prepareStatement("insert into brother_activity values(?, ?, ?, now(), ?)"));
        queries.put("select_max_logg_id", conn.prepareStatement("SELECT MAX(entry_id) FROM brother_activity"));
        queries.put("insert_order_execution", conn.prepareStatement("INSERT INTO order_execution values(?, ?, ?, ?)"));
        queries.put("get_max_execution_id", conn.prepareStatement("SELECT MAX(entry_id) FROM order_execution"));
        queries.put("get_max_orders_id", conn.prepareStatement("SELECT MAX(order_id) FROM battle_order"));
        queries.put("insert_battle_order", conn.prepareStatement("INSERT INTO battle_order values(?, ?, ?, ?, ?)"));
        queries.put("edit_battle_order_text", conn.prepareStatement("UPDATE battle_order SET order_text = ? WHERE order_id = ?"));
        queries.put("edit_execution_report", conn.prepareStatement("UPDATE order_execution SET brief_report = ? WHERE order_id = ?"));
        queries.put("edit_brother", conn.prepareStatement("update brother set name=?, rank_battle=?, rank_work=?, points=?, unit_id=? where brother_id=?"));
        queries.put("see_brother_log", conn.prepareStatement("select * from brother_activity join activity " +
                " on brother_activity.activity_id = activity.activity_id " +
                " where brother_id = ? order by creation_date desc limit 50 "));
        queries.put("insert_report", conn.prepareStatement("insert into battle_report values (?,?,?,now(),?,0)"));
        queries.put("get_reports_by_event", conn.prepareStatement("select * from battle_report join brother on brother.brother_id = " +
                "battle_report.author_id where battle_report.event_id = ? and brother.rank_battle <= ?"));
        queries.put("select_max_report_id", conn.prepareStatement("select max(report_id) from battle_report"));
        queries.put("edit_report", conn.prepareStatement("update battle_report set report_text = ? where report_id = ?"));
        queries.put("create_event", conn.prepareStatement("insert into chapter_event values (?,?,?,?)"));
        queries.put("select_max_event_id", conn.prepareStatement("select max(event_id) from chapter_event"));
    }
}
